import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class ReadAndPrintXMLFile {

	public static Connection getOracleDBConnection() {
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			Connection dbconnection = DriverManager.getConnection(
					"jdbc:oracle:thin:@134.106.56.126:1521/dbpun14.offis.de ",
					"badb2_2", "badb2_2");

			return dbconnection;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return null;
		}

	}

	public static File[] getInputFolder(String path) {
		File f = new File(path);
		return f.listFiles();
	}

	public static DocumentBuilder getDocBuilder() {
		DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory
				.newInstance();
		try {
			DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
			return docBuilder;
		} catch (ParserConfigurationException e) {
			return null;
		}
	}
	
	public static boolean isXMLfile(File fl) {
		if (fl.getName().endsWith(".xml")) {
			return true;
		} else {
			return false;
		}
	}
	
	public static boolean isSQLfile(File fl) {
		if (fl.getName().endsWith(".sql")) {
			return true;
		} else {
			return false;
		}
	}
	
	public static ArrayList<String> loadSQLfile(File f) {
		if (isSQLfile(f)) {
	        String fileString = "";
	        FileInputStream fis = null;
	        BufferedInputStream bis = null;
	        DataInputStream dis = null;
	        try {
	        	BufferedReader in = new BufferedReader(new FileReader(f));
	    		String zeile = null;
	    		while ((zeile = in.readLine()) != null) {
	    			
	    			fileString = fileString + zeile;
	    		}
	    		in.close();
	    		

	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        
	        fileString.replace("/n","/0");
	        fileString.replace("/t","/0");
	        fileString.replace("/r", "/0");
	        ArrayList<String> commandsList = new ArrayList<String>();
	        
	        for(String c : fileString.split(";")){
	        	if((c.indexOf("INSERT")>=0) ){
	        		int i = c.indexOf("INSERT");
	        		
	        		commandsList.add(c.substring(i));
	        	}
	        }
	        
	        return commandsList;
		} else {
			return null;
		}
    }
	
	public static ArrayList[] loadXMLfiles(File[] files) {
		DocumentBuilder docBuilder = getDocBuilder();
		ArrayList[] tableList = new ArrayList[5];
		if (docBuilder != null) {
			for (File fl : files) {
				if (isXMLfile(fl)) {
					try {
						Document doc = docBuilder.parse(fl);
						doc.normalize();
						String tabelle = (doc.getChildNodes().item(0)
								.getChildNodes().item(1).getNodeName());
						int prio = getPriority(tabelle);
						if (prio >= 0) {
							ArrayList<String> insertList = new ArrayList<String>();
							NodeList tupelList = doc.getChildNodes().item(0)
									.getChildNodes();
							for (int i = 0; tupelList.getLength() > i; i++) {
								if (tupelList.item(i).getNodeName() != "#text") {
									String table = tupelList.item(i)
											.getNodeName();
									String insertBefehl = "INSERT INTO "
											+ table + "(";
									String values = "VALUES(";
									NodeList tupel = tupelList.item(i)
											.getChildNodes();
									for (int j = 0; tupel.getLength() > j; j++) {
										if (tupel.item(j).getNodeName() != "#text") {
											insertBefehl = insertBefehl
													+ tupel.item(j)
															.getNodeName();
											values = values
													+ transformStrings(tupel
															.item(j)
															.getTextContent());
											if (tupel.getLength() > (j + 2)) {
												insertBefehl = insertBefehl
														+ ",";
												values = values + ",";
											}
										}
									}
									insertBefehl = insertBefehl + ")";
									values = values + ")";
									insertList.add(insertBefehl + " " + values);
								}
							}
							tableList[prio] = insertList;
							File loadedFile = new File(fl.getParent()+"/loadedFiles/"+fl.getName());
							fl.renameTo(loadedFile);
						}
					} catch (SAXException e) {
	
					} catch (IOException e) {
					}
				}
			}
		}
		return tableList;
	}

	public static int getPriority(String tablename) {
		int prio = -1;
		if (tablename.equals("KUNDE")) {
			prio = 0;
		} else if (tablename.equals("PERSONAL")) {
			prio = 1;
		} else if (tablename.equals("TEILESTAMM")) {
			prio = 2;
		} else if (tablename.equals("AUFTRAG")) {
			prio = 3;
		} else if (tablename.equals("AUFTRAGSPOSTEN")) {
			prio = 4;
		}

		return prio;
	}

	public static void main(String argv[]) {
		Connection con = getOracleDBConnection();
		if (con != null) {
			System.out.println("DB funktioniert");
			File[] files = getInputFolder("C:/Workspace/db2_projekt/xml");
			for (File fl: files) {
				if (loadSQLfile(fl) != null) {
					
					ArrayList<String> inserts = loadSQLfile(fl);
					File loadedFile = new File(fl.getParent()+"/loadedFiles/"+fl.getName());
					fl.renameTo(loadedFile);

					for (String insert : inserts) {
						try {
							Statement stmt = con.createStatement();
							stmt.execute(insert);
							con.commit();
						} catch (SQLException e) {
							System.out.println(e.getMessage());
						}
						System.out.println(insert);	
					}				
				
				}
			}
			
			ArrayList[] xmlList = loadXMLfiles(files);
			
			for (ArrayList<String> table : xmlList) {
				if (table != null) {
					for (String insert : table) {
						try {
							Statement stmt = con.createStatement();
							stmt.execute(insert);
							con.commit();
						} catch (SQLException e) {
							System.out.println(e.getMessage());
						}
						System.out.println(insert);	
					}
				}
			}
			// Daten aus dem Arbeitsbereich in das Warehouse laden
			loadingWarehouse(con);
		}
	}
	

	public static void loadingWarehouse(Connection dbconnection) {
		CallableStatement cstmt = null;
		try {

			// Connect to the local database
		
			String sSQL = "BEGIN LOAD_SNOW(); " + "END;";
			cstmt = dbconnection.prepareCall(sSQL);
			cstmt.execute();
			cstmt.close();
			sSQL = "BEGIN LOAD_STAR(); " + "END;";
			cstmt = dbconnection.prepareCall(sSQL);
			cstmt.execute();
			cstmt.close();
			dbconnection.close();

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			cstmt = null;
		}
	}

	public static String transformStrings(String alt) {
		if (alt.matches("[0-9.]+")) {
			return alt;
		} else if (alt.indexOf("T00:00:00") > 1) {
			int pos = alt.indexOf("T00:00:00");
			alt = alt.substring(0, pos);
			return "Date '" + alt + "'";

		} else {

			alt = alt.replace("  ", "");
			return "'" + alt + "'";
		}
	}

}// end of main
